﻿using ShSoft.Common.PoweredByLee;
using SlamDunk.CMS.Model.DataModel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace SlamDunk.CMS.DAL
{
    public class PhotoDal
    {
        #region # 静态构造器

        /// <summary>
        /// SqlHelper
        /// </summary>
        private static readonly SqlHelper _SqlHelper;

        /// <summary>
        /// 静态构造器
        /// </summary>
        static PhotoDal()
        {
            _SqlHelper = new SqlHelper(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
        }

        #endregion

        #region 01.根据相册Id查询图片记录条数
        /// <summary>
        /// 根据相册Id查询图片记录条数
        /// </summary>
        /// <param name="albumId">相册Id</param>
        /// <returns>符合条件的图片记录条数</returns>
        public int GetCount(int albumId)
        {
            string sql = "SELECT COUNT(*) FROM dbo.Photos WHERE DelFlag = 0";

            if (albumId != 0)
            {
                sql = string.Format("{0} AND AlbumId = {1}", sql, albumId);
            }

            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 02.根据相册Id分页查询图片列表
        /// <summary>
        /// 根据相册Id分页查询图片列表
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">结束行</param>
        /// <param name="albumId">相册Id</param>
        /// <returns>图片列表</returns>
        public List<Photo> GetModelList(int start, int end, int albumId)
        {
            List<Photo> list = new List<Photo>();
            string sql = "SELECT *, ROW_NUMBER() OVER(ORDER BY AlbumId ASC, Sort DESC, AddTime DESC) AS RowIndex  FROM dbo.Photos WHERE DelFlag = 0";

            #region 非空校验
            if (albumId != 0)
            {
                sql = string.Format("{0} AND AlbumId = {1}", sql, albumId);
            }
            #endregion

            //分页处理
            sql = string.Format("SELECT * FROM ({0}) AS T WHERE T.RowIndex >= @Start AND T.RowIndex <= @End", sql);

            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@Start", start),
                new SqlParameter("@End", end)
            };

            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, args))
            {
                while (reader.Read())
                {
                    list.Add(this.ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 03.根据相册Id查询图片列表
        /// <summary>
        /// 根据相册Id查询图片列表
        /// </summary>
        /// <param name="albumId">相册Id</param>
        /// <returns>图片列表</returns>
        public List<Photo> GetModelList(int albumId)
        {
            List<Photo> list = new List<Photo>();
            string sql = "SELECT * FROM dbo.Photos WHERE DelFlag = 0 AND AlbumId = @AlbumId ORDER BY AlbumId ASC, Sort DESC, AddTime DESC";

            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@AlbumId", albumId)))
            {
                while (reader.Read())
                {
                    list.Add(this.ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 01.添加方法
        /// <summary>
        /// 添加一个实体对象
        /// </summary>
        /// <param name="photo">要添加的实体对象</param>
        /// <returns>添加完毕后的实体对象</returns>
        public Photo Add(Photo photo)
        {
            string sql = "INSERT INTO Photos (AlbumId, PhotoName, Description, Url, ImagePath, ThumbPath, Sort, DelFlag, AddTime)  output inserted.Id VALUES (@AlbumId, @PhotoName, @Description, @Url, @ImagePath, @ThumbPath, @Sort, @DelFlag, @AddTime)";
            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@AlbumId", ToDBValue(photo.AlbumId)),
                new SqlParameter("@PhotoName", ToDBValue(photo.PhotoName)),
                new SqlParameter("@Description", ToDBValue(photo.Description)),
                new SqlParameter("@Url", ToDBValue(photo.Url)),
                new SqlParameter("@ImagePath", ToDBValue(photo.ImagePath)),
                new SqlParameter("@ThumbPath", ToDBValue(photo.ThumbPath)),
                new SqlParameter("@Sort", ToDBValue(photo.Sort)),
                new SqlParameter("@DelFlag", ToDBValue(photo.DelFlag)),
                new SqlParameter("@AddTime", ToDBValue(photo.AddTime)),
            };
            //根据newId返回新添加的实体对象
            int newId = (int)_SqlHelper.ExecuteScalar(sql, args);
            return this.GetModel(newId);
        }
        #endregion

        #region 02.删除方法（物理删除）
        /// <summary>
        /// 删除一个实体对象
        /// </summary>
        /// <param name="id">要删除的实体对象Id</param>
        /// <returns>受影响的行数</returns>
        public int PhysicalDelete(int id)
        {
            string sql = "DELETE FROM Photos WHERE Id = @Id";
            SqlParameter arg = new SqlParameter("@Id", id);
            return _SqlHelper.ExecuteNonQuery(sql, arg);
        }
        #endregion

        #region 04.修改方法
        /// <summary>
        /// 修改一个实体对象
        /// </summary>
        /// <param name="photo">构造好的要修改的实体对象</param>
        /// <returns>受影响的行数</returns>
        public int Update(Photo photo)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("UPDATE Photos SET AlbumId = @AlbumId");
            sql.Append(", PhotoName = @PhotoName");
            sql.Append(", Description = @Description");
            sql.Append(", Url = @Url");
            sql.Append(", ImagePath = @ImagePath");
            sql.Append(", ThumbPath = @ThumbPath");
            sql.Append(", Sort = @Sort");
            sql.Append(", DelFlag = @DelFlag");
            sql.Append(", AddTime = @AddTime");
            sql.Append(" WHERE Id = @Id");
            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@Id", photo.Id)
                ,new SqlParameter("@AlbumId", ToDBValue(photo.AlbumId))
                ,new SqlParameter("@PhotoName", ToDBValue(photo.PhotoName))
                ,new SqlParameter("@Description", ToDBValue(photo.Description))
                ,new SqlParameter("@Url", ToDBValue(photo.Url))
                ,new SqlParameter("@ImagePath", ToDBValue(photo.ImagePath))
                ,new SqlParameter("@ThumbPath", ToDBValue(photo.ThumbPath))
                ,new SqlParameter("@Sort", ToDBValue(photo.Sort))
                ,new SqlParameter("@DelFlag", ToDBValue(photo.DelFlag))
                ,new SqlParameter("@AddTime", ToDBValue(photo.AddTime))
            };
            return _SqlHelper.ExecuteNonQuery(sql.ToString(), args);
        }
        #endregion

        #region 05.根据主键获取单个实体对象
        /// <summary>
        /// 根据主键获取单个实体对象
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>单个实体对象</returns>
        public Photo GetModel(int id)
        {
            string sql = "SELECT * FROM Photos WHERE Id = @Id";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", id)))
            {
                if (reader.Read())
                {
                    return ToModel(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 17.根据IDataReader对象返回实体对象方法
        /// <summary>
        /// 根据IDataReader对象返回实体对象方法
        /// </summary>
        /// <param name="reader">IDataReader对象</param>
        /// <returns>实体对象</returns>
        public Photo ToModel(IDataReader reader)
        {
            Photo photo = new Photo();
            photo.Id = (int)ToModelValue(reader as SqlDataReader, "Id");
            photo.AlbumId = (int)ToModelValue(reader as SqlDataReader, "AlbumId");
            photo.PhotoAlbum = new PhotoAlbumDal().GetModel(photo.AlbumId);
            photo.PhotoName = (string)ToModelValue(reader as SqlDataReader, "PhotoName");
            photo.Description = (string)ToModelValue(reader as SqlDataReader, "Description");
            photo.Url = (string)ToModelValue(reader as SqlDataReader, "Url");
            photo.ImagePath = (string)ToModelValue(reader as SqlDataReader, "ImagePath");
            photo.ThumbPath = (string)ToModelValue(reader as SqlDataReader, "ThumbPath");
            photo.Sort = (int)ToModelValue(reader as SqlDataReader, "Sort");
            photo.DelFlag = (bool)ToModelValue(reader as SqlDataReader, "DelFlag");
            photo.AddTime = (DateTime)ToModelValue(reader as SqlDataReader, "AddTime");
            return photo;
        }
        #endregion

        #region 18.C#值转数据库值空值处理
        /// <summary>
        /// C#值转数据库值空值处理
        /// </summary>
        /// <param name="value">C#值</param>
        /// <returns>处理后的数据库值</returns>
        public object ToDBValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }
        #endregion

        #region 19.数据库值转C#值空值处理
        /// <summary>
        /// 数据库值转C#值空值处理
        /// </summary>
        /// <param name="reader">IDataReader对象</param>
        /// <param name="columnName">列名</param>
        /// <returns>C#值</returns>
        public object ToModelValue(IDataReader reader, string columnName)
        {
            if (reader.IsDBNull(reader.GetOrdinal(columnName)))
            {
                return null;
            }
            else
            {
                return reader[columnName];
            }
        }
        #endregion
    }
}
